package com.mach.platform.components;

import com.mach.platform.domain.form.HourForm;
import com.mach.platform.domain.form.MinuteForm;
import com.mach.platform.repository.HourFormRepo;
import com.mach.platform.repository.MinuteFormRepo;
import lombok.extern.slf4j.Slf4j;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.transaction.Transactional;
import java.util.List;

/**
 * @author Administrator
 * @Description: 定时任务实行地方
 * @Package: com.mach.platform.components
 * @time 2018/12/14 0014 15:48
 */
@Slf4j
@Component
public class SchdulerWorker {

	@Autowired
	private MinuteFormRepo minuteFormRepo;
	@Autowired
	private HourFormRepo   hourFormRepo;

	@PersistenceContext
	EntityManager em;

	/**
	 * 采集分钟的数据
	 * @param sTime 上一分钟的开始时间
	 * @param eTime 上一分钟的结束时间
	 * @param nsTime 当前分钟的开始时间
	 * @param nsTime  当前分钟的结束时间
	 */
	@Transactional(rollbackOn = Exception.class)
	public void pickupDataForMinuteForm (String sTime, String eTime,String nsTime,String neTime) {
		// 算法： 上一分钟内最大的数据  减去 最小的数据
	/*	String sql =
				"SELECT dev_id as devId,the_time as time,data_type as dataType,dvalue as firstNum,(max(dvalue) -  min(dvalue)) as useNum"
						+ " FROM iot_dev_data_win where 1 = 1 " + "and the_time BETWEEN '" + sTime + "' AND '" + eTime
						+ "' GROUP BY dev_id,data_type";*/


	//算法：提取当前分钟开始记录数据  减去 上一分钟 开始的数据
	String sql = "SELECT dev_id                      as devId,\n" + "       the_time                    as time,\n"
			             + "       data_type                   as dataType,\n"
			             + "       dvalue                      as firstNum,\n"
			             + "       (max(dvalue) - min(dvalue)) as useNum\n" + "from\n" + "((SELECT *\n"
			             + "FROM iot_dev_data_win\n"
			             + "where the_time BETWEEN '"+sTime+"' AND '"+eTime+"'\n"
			             + " GROUP BY dev_id, data_type)\n" + "union all\n" + "(SELECT *\n" + "FROM iot_dev_data_win\n"
			             + "where the_time BETWEEN '"+ nsTime+"' AND '"+ neTime+"'\n"
			             + " GROUP BY dev_id, data_type)) TT GROUP BY dev_id, data_type";

		Query nativeQuery = em.createNativeQuery(sql);
		List<MinuteForm> dbResult = nativeQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(MinuteForm.class)).list();

		if (dbResult.size() == 0) {
			log.info("上一分钟没有采集数据！");
			return;
		}
		minuteFormRepo.saveAll(dbResult);
		log.info("上一分钟的数据提取完毕！");
	}

	/**
	 * 采集小时的数据
	 */
	@Transactional(rollbackOn = Exception.class)
	public void pickupDataForHourForm (String sTime, String eTime, String nsTime, String neTime) {
		// 按照分组内最大减最小 获取数据
		/*String sql =
				"SELECT dev_id as devId,the_time as time,data_type as dataType,dvalue as firstNum,(max(dvalue) -  min(dvalue)) as useNum"
						+ " FROM iot_dev_data_win where 1 = 1 " + "and the_time BETWEEN '" + sTime + "' AND '" + eTime
						+ "' GROUP BY dev_id,data_type";*/

		//算法：提取当前分钟开始记录数据  减去 上一分钟 开始的数据
		String sql = "SELECT dev_id                      as devId,\n" + "       the_time                    as time,\n"
				             + "       data_type                   as dataType,\n"
				             + "       dvalue                      as firstNum,\n"
				             + "       (max(dvalue) - min(dvalue)) as useNum\n" + "from\n" + "((SELECT *\n"
				             + "FROM iot_dev_data_win\n" + "where the_time BETWEEN '" + sTime + "' AND '" + eTime
				             + "'\n" + " GROUP BY dev_id, data_type)\n" + "union all\n" + "(SELECT *\n" + "FROM iot_dev_data_win\n"
				             + "where the_time BETWEEN '" + nsTime + "' AND '" + neTime + "'\n" + " GROUP BY dev_id, data_type)) TT GROUP BY dev_id, data_type";


		Query nativeQuery = em.createNativeQuery(sql);
		List<HourForm> dbResult = nativeQuery.unwrap(NativeQueryImpl.class)
		                                     .setResultTransformer(Transformers.aliasToBean(HourForm.class)).list();

		if (dbResult.size() == 0) {
			log.info("上一小时没有采集数据！");
			return;
		}

		hourFormRepo.saveAll(dbResult);
		log.info("上一小时的数据提取完毕！");
	}
}
